﻿--提取审批列表
CREATE PROCEDURE [dbo].[proc_Employee_CompanyMove_GetAuditList]
(
	@startIndex nvarchar(20),
	@endIndex nvarchar(20),
	@ename nvarchar(50),
	@companyid nvarchar(400),
	@audit nvarchar(4)
)
as
DECLARE @sql NVARCHAR(4000)
DECLARE @swhere NVARCHAR(4000)
SET @swhere=' EC.Proposer is not null'
SET @swhere=@swhere+' and (case when EC.audit=1 then 1  when  EC.audit=0 and EC.auditperson is null then 0'
SET @swhere=@swhere+' when EC.audit=0 and EC.auditperson is not null then 2 end) like ''%'+@audit+'%'''	   
IF(@CompanyId<>'0')
SET @swhere=@swhere+' and EC.ACompanyId in('+@CompanyId+')'
IF(@EName IS NOT null)
SET @swhere=@swhere+' and (select EName from Employee E where EC.EId=E.Id) like ''%'+@EName+'%'''
SET @sql=
		+' ;WITH list As(Select ROW_NUMBER() OVER (Order By EC.audit,EC.auditTime DESC)AS Row,'
		+'	EC.Id,'
		+'	(select ENumber from Employee E where EC.EId=E.Id) as ENumber,'
		+'	(select ETempNumber from Employee E where EC.EId=E.Id) as ETempNumber,'
		+'	EC.EId,'
		+'	(select EName from Employee E where EC.EId=E.Id) as EName,'
		+'	(select Sex from Employee E where EC.EId=E.Id) as Sex,'
		+'	(select CompanyName from Company where id=EC.Bcompanyid) as CompanyName,'
		+'	/*(select DName from Department where id=EC.Bdepartmentid)*/ BDepartmentname as DName,'
		+'	(select PostName from Post P where id=(select PostId from employee E where EC.EId=E.Id)) as PostName,'
		+'	(select Title from Grade G where id=(select GradeId from employee E where EC.EId=E.Id)) as GradeName,'					
		+'	EC.Audit,'
		+'	EC.AuditPerson,'
		+'	(select CompanyName from Company where id=EC.ACompanyId) AS BCompanyName,'
		+'	/*(select DName from Department where id=EC.ADepartmentId) as BDName*/ ADepartmentname as BDName,'
		+'	(select EName from Employee E where EC.AuditPerson=E.UserName) as AuditPersonName,'
		+'	EC.AuditTime,'
		+'	ISNULL(EC.Execdate,''1900-01-01'') AS Execdate,'
		+'  ISNULL(EC.IsExec,0) AS IsExec'
		+'	From Employee_CompanyMove EC '
		+'	Where '
		+@swhere
		+'  ) '
		+' Select *,(SELECT COUNT(0) FROM list) AS coun From list Where Row between '+ @StartIndex+' and '+@EndIndex
EXEC(@sql)
print (@sql)

----提取审批列表
--ALTER proc [dbo].[proc_Employee_CompanyMove_GetAuditList]
--(
--	@startIndex int,
--	@endIndex int,
--	@ename nvarchar(50),
--	@companyid int,
--	@audit nvarchar(4)
--)
--as

--;WITH list As(Select ROW_NUMBER() OVER (Order By EC.Id DESC)AS Row,
--			EC.Id,
--			(select ENumber from Employee E where EC.EId=E.Id) as ENumber,
--			(select ETempNumber from Employee E where EC.EId=E.Id) as ETempNumber,
--			EC.EId,
--			(select EName from Employee E where EC.EId=E.Id) as EName,
--			(select Sex from Employee E where EC.EId=E.Id) as Sex,
--			(select CompanyName from Company where id=EC.Bcompanyid) as CompanyName,
--			(select DName from Department where id=EC.Bdepartmentid) as DName,
--			(select PostName from Post P where id=(select PostId from employee E where EC.EId=E.Id)) as PostName,
--			(select Title from Grade G where id=(select GradeId from employee E where EC.EId=E.Id)) as GradeName,
					
--			EC.Audit,
--			EC.AuditPerson,
--			(select CompanyName from Company where id=EC.ACompanyId) AS BCompanyName,
--			(select DName from Department where id=EC.ADepartmentId) as BDName,
--			(select EName from Employee E where EC.AuditPerson=E.UserName) as AuditPersonName,
--			EC.AuditTime
--			From Employee_CompanyMove EC 
--		Where (@CompanyId=0 Or (select companyid from employee E where EC.EId=E.Id)=@CompanyId) And (@EName is null Or (select EName from Employee E where EC.EId=E.Id) like '%'+ @EName +'%') 
--					and (case when EC.audit=1 then '1' 
--						when EC.audit=0 and EC.auditperson is null then '0'
--						when EC.audit=0 and EC.auditperson is not null then '1' end) like '%'+@audit+'%'
--					and EC.Proposer is not null
--		)
--		Select *,(SELECT COUNT(0) FROM list) AS coun From list Where Row between @StartIndex and @EndIndex
RETURN
